package com.dycong.common.common;

import com.dycong.common.common.interAnno.Multiply;
import com.dycong.common.reflect.Reflect.CommandUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.format.annotation.DateTimeFormat;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * 在字段头 可添加 DateTimeFormat注解
 * 如果是枚举 则会直接引用displayName
 * Created by zhengjiang on 2015/3/10.
 */
public class ExcelUtils<T> {

    private static Logger log = Logger.getLogger(ExcelUtils.class);
    private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");

    /**
     * 导入Excel表格
     *
     * @param attribute 中的元素是一个长度为2的数组，其中arr [0]是Excel列名称  arr[1]表示的是对象的属性名称
     * @param objs      导出的对象集合
     * @param os        输出流
     */
    public void toExcelAjax(List<String[]> attribute, List<T> objs, OutputStream os) {
        if (objs == null) {
            return;
        }
        if (objs.isEmpty()) {
            return;
        }
        try {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            HSSFSheet hssfsheet = hssfworkbook.createSheet(getimageId());
            int j = 0;
            //设置表头
            HSSFRow hssfrow = hssfsheet.createRow(j);
            j++;

            int i = 0;

            for (String[] arr : attribute) {
                hssfrow.createCell(i).setCellValue(arr[0]);
                i++;
            }

            //设置表中内容
            HSSFRow row = null;
            for (T obj : objs) {
                row = hssfsheet.createRow(j);
                i = 0;
                for (String[] arr : attribute) {
                    String fileName = arr[1];
                    Object value = getField0(obj, fileName);
                    if (value != null) {
//                        if (value instanceof Date){
//                            //todo
//                        }else
                        row.createCell(i).setCellValue(value.toString());
                    } else {
                        row.createCell(i).setCellValue("");
                    }
                    i++;
                }
                j++;
            }

            for (int n = 0; n < attribute.size(); n++) {
                hssfsheet.setColumnWidth(n, 5000);
            }


            hssfworkbook.write(os);
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e);
        }
    }

    public Object getField0(Object target, String fieldName) throws Exception {
        Field field = findField(target.getClass(), fieldName);
        Object result = getField1(target, field);
        if (result instanceof Enum) {
            Field displayName = findField(result.getClass(), "displayName");
            if (displayName != null) {
                return getField1(result, displayName);
            }
        } else if (result instanceof Date) {
            DateTimeFormat dateTimeFormat = field.getAnnotation(DateTimeFormat.class);
            if (dateTimeFormat != null) {
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat(dateTimeFormat.pattern());
                return simpleDateFormat.format(result);
            }
        } else if (result instanceof Integer) {
            Multiply multiply = field.getAnnotation(Multiply.class);
            if (multiply != null) {
                float f = multiply.pattern();
                return ((Integer) result * f);
            }
        }
        return result;
    }

    public Object getField1(Object target, Field field) {
        try {
            boolean accessible = field.isAccessible();
            field.setAccessible(true);
            Object result = field.get(target);
            field.setAccessible(accessible);
            return result;
        } catch (Exception e) {
            throw new IllegalStateException("获取对象的属性[" + field.getName()
                    + "]值失败", e);
        }
    }

    public String getimageId() {
        Date data = new Date();
        String timestr = data.toString();
        timestr = timestr.substring(4);
        String str = new String(" CST ");
        timestr = timestr.replaceAll(str, " ");
        timestr = timestr.replaceAll(" PDT ", " ");
        timestr = timestr.replaceAll(" ", "");//去掉所有的空格
        timestr = timestr.replaceAll(":", "");//去掉所有的":"
        return timestr;
    }


    public Field findField(Class<?> targetClass, String fieldName) throws Exception {
        if (targetClass == null) {
            throw new Exception(fieldName);
        }
        if (null == fieldName || 0 == fieldName.length()) {
            throw new Exception(fieldName);
        }
        for (Field field : getAllDeclaredField(targetClass)) {
            if (fieldName.equals(field.getName())) {
                return field;
            }
        }
        return null;
    }

    public List<Field> getAllDeclaredField(Class<?> targetClass,
                                           String... excludeFieldNames) {
        List<Field> fields = new ArrayList<Field>();
        for (Field field : targetClass.getDeclaredFields()) {
            if (contains(excludeFieldNames, field.getName())) {
                continue;
            }
            fields.add(field);
        }
        Class<?> parentClass = targetClass.getSuperclass();
        if (parentClass != Object.class) {
            fields.addAll(getAllDeclaredField(parentClass, excludeFieldNames));
        }
        return fields;
    }

    public <T> Boolean contains(T[] elements, T elementToFind) {
        List<T> elementsList = Arrays.asList(elements);
        return elementsList.contains(elementToFind);
    }

    public void toExcelAjax(List<String[]> attribute, List<T> objs, List<String[]> header, OutputStream os) {
        if (objs == null) {
            return;
        }
        if (objs.isEmpty()) {
            return;
        }
        try {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            HSSFSheet hssfsheet = hssfworkbook.createSheet(getimageId());
            int j = 0;
            //设置表头
            HSSFRow hssfrow = hssfsheet.createRow(j);
            j++;

            int i = 0;
            for (String[] arr1 : header) {
                hssfrow.createCell(i).setCellValue(String.valueOf(arr1[0]));
                i++;
            }
            HSSFRow hr = hssfsheet.createRow(j);
            i = 0;
            for (String[] arr : attribute) {
                hr.createCell(i).setCellValue(String.valueOf(arr[0]));
                i++;
            }
            j++;

            //设置表中内容
            HSSFRow row = null;
            for (T obj : objs) {
                row = hssfsheet.createRow(j);
                i = 0;
                for (String[] arr : attribute) {
                    String fileName = arr[1];
                    Object value = getField0(obj, fileName);
                    if (value != null) {
                        row.createCell(i).setCellValue(value.toString());
                    } else {
                        row.createCell(i).setCellValue("");
                    }
                    i++;
                }
                j++;
            }

            for (int n = 0; n < attribute.size(); n++) {
                hssfsheet.setColumnWidth(n, 5000);
            }

            hssfsheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 2));
            hssfworkbook.write(os);
            os.close();
        } catch (Exception e) {
            log.error(e);
        }
    }

    /**
     * @param attribute 中的元素是一个长度为2的数组，其中arr [0]是Excel列名称  arr[1]表示的是对象的属性名称
     * @param objs
     * @param header
     * @param os
     * @see 分多个sheet的表格导出,每个sheet以 Map<String, List<T>>objs中String为名
     */
    public void toExcelAjax(List<String[]> attribute, Map<String, List<T>> objs, List<String[]> header, OutputStream os) {
        if (objs == null) {
            return;
        }
        if (objs.isEmpty()) {
            return;
        }
        try {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            for (String name : objs.keySet()) {

                HSSFSheet hssfsheet = hssfworkbook.createSheet(name);
                int j = 0;
                //设置表头
                HSSFRow hssfrow = hssfsheet.createRow(j);
                j++;

                int i = 0;
                for (String[] arr1 : header) {
                    hssfrow.createCell(i).setCellValue(String.valueOf(arr1[0]));
                    i++;
                }
                HSSFRow hr = hssfsheet.createRow(j);
                i = 0;
                for (String[] arr : attribute) {
                    hr.createCell(i).setCellValue(String.valueOf(arr[0]));
                    i++;
                }
                j++;

                //设置表中内容
                HSSFRow row = null;
                for (T obj : objs.get(name)) {
                    row = hssfsheet.createRow(j);
                    i = 0;
                    for (String[] arr : attribute) {
                        String fileName = arr[1];
                        Object value = getField0(obj, fileName);
                        if (value != null) {
                            row.createCell(i).setCellValue(value.toString());
                        } else {
                            row.createCell(i).setCellValue("");
                        }
                        i++;
                    }
                    j++;
                }

                for (int n = 0; n < attribute.size(); n++) {
                    hssfsheet.setColumnWidth(n, 5000);
                }
                hssfsheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 2));
            }
            hssfworkbook.write(os);
            os.close();
        } catch (Exception e) {
            log.error(e);
        }
    }

    /*toExcelZip时暂存文件的服务器路径*/
    final static String SERVER_PATH = "toExcelZip时暂存文件的服务器路径";
    /*单个Excel最大条数*/
    final static Integer ITEM_MAX = 30000;

    /**
     * @param attribute  中的元素是一个长度为2的数组，其中arr [0]是Excel列名称  arr[1]表示的是对象的属性名称
     * @param objs
     * @param header
     * @param os
     * @param serverPath
     * @see 数据量过大,导出多个表格并压缩->下载
     */
    public void toExcelZip(List<String[]> attribute, List<T> objs, List<String[]> header, OutputStream os, String file) {
        if (objs == null) {
            return;
        }
        if (objs.isEmpty()) {
            return;
        }
        String path = simpleDateFormat.format(new Date());
        List<File> srcfile = new ArrayList<File>();
        List[] lists = StringUtil.splitListByStep(objs, ITEM_MAX);
        try {
            for (int k = 0; k < lists.length; k++) {
                List<T> list = lists[k];
                FileOutputStream out = new FileOutputStream(SERVER_PATH + path + "\\" + file + k + ".xls");

                HSSFWorkbook hssfworkbook = new HSSFWorkbook();
                HSSFSheet hssfsheet = hssfworkbook.createSheet(getimageId());
                int j = 0;
                //设置表头
                HSSFRow hssfrow = hssfsheet.createRow(j);
                j++;

                int i = 0;
                for (String[] arr1 : header) {
                    hssfrow.createCell(i).setCellValue(String.valueOf(arr1[0]));
                    i++;
                }
                HSSFRow hr = hssfsheet.createRow(j);
                i = 0;
                for (String[] arr : attribute) {
                    hr.createCell(i).setCellValue(String.valueOf(arr[0]));
                    i++;
                }
                j++;

                //设置表中内容
                HSSFRow row = null;
                for (T obj : list) {
                    row = hssfsheet.createRow(j);
                    i = 0;
                    for (String[] arr : attribute) {
                        String fileName = arr[1];
                        Object value = getField0(obj, fileName);
                        if (value != null) {
                            row.createCell(i).setCellValue(value.toString());
                        } else {
                            row.createCell(i).setCellValue("");
                        }
                        i++;
                    }
                    j++;
                }

                for (int n = 0; n < attribute.size(); n++) {
                    hssfsheet.setColumnWidth(n, 5000);
                }

                hssfsheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 2));
                hssfworkbook.write(out);
                out.close();
                srcfile.add(new File(SERVER_PATH + path + "/" + file + k + ".xls"));
            }
            File zipfile = new File(SERVER_PATH + path + "/" + file + ".zip");
            this.zipFiles(srcfile, zipfile);
            //弹出下载框供用户下载
            this.downFile(os, SERVER_PATH + path + "/", file + ".zip");
        } catch (Exception e) {
            log.error(e);
        }
    }


//TODO 读Excel--->Objects OK

    final static String DATE_PATTERN = "yyyy-MM-dd HH:mm:ss";

    public <T> Collection<T> fromExcel(Class<T> clazz, Map<String, String> attribute, InputStream inputStream) {
        return fromExcel(clazz, 0, attribute, DATE_PATTERN, inputStream);
    }

    public <T> Collection<T> fromExcel(Class<T> clazz, Integer attributeIndex, Map<String, String> attribute, InputStream inputStream) {
        return fromExcel(clazz, attributeIndex, attribute, DATE_PATTERN, inputStream);
    }

    public <T> Collection<T> fromExcel(Class<T> clazz, Map<String, String> attribute, String datePattern, InputStream inputStream) {
        return fromExcel(clazz, 0, attribute, datePattern, inputStream);
    }

    /**
     * @param clazz
     * @param attributeIndex 属性行所在的index;
     * @param attribute      可以不按照顺序，列名与Object字段的对应关系
     * @param datePattern
     * @param inputStream
     * @param <T>
     * @return
     */
    public <T> Collection<T> fromExcel(Class<T> clazz, Integer attributeIndex, Map<String, String> attribute, String datePattern, InputStream inputStream) {
        HSSFWorkbook workBook = null;
        try {
            workBook = new HSSFWorkbook(inputStream);
        } catch (IOException e) {
            log.error(e.toString(), e);
        }
        List<T> rowList = new ArrayList<>();
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.rowIterator();

        Map<Integer, String> titleMap = new HashMap<>();

        List<T> objectList = new ArrayList<>();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (isAllNull(row))
                continue;
            if (row.getRowNum() == attributeIndex) {
                // 解析map用的key,就是excel标题行
                Iterator<Cell> cellIterator = row.cellIterator();
                int index = 0;
                /*将行首字符转换为代表的column名与列的index组成的Map*/
                while (cellIterator.hasNext() && index < attribute.size()) {
                    String value = cellIterator.next().getStringCellValue();
                    titleMap.put(index, attribute.get(value));
                    index++;
                }
                continue;
            } else if (row.getRowNum() >= attributeIndex) {
                Map<String, String> map = new HashMap<String, String>();
                Iterator<Cell> cellIterator = row.cellIterator();
                int index = 0;
                while (cellIterator.hasNext() && index < titleMap.size()) {
                    String value = cellIterator.next().getStringCellValue();
                    String key = titleMap.get(index);
                    map.put(key, value);
                    index++;
                }
                objectList.add((T) CommandUtil.map2Object(map, clazz.getName(), datePattern));
            }
        }
        return objectList;
    }

    //TODO 读Excel--->List<Map<String,Object>>

    /******************************************/
    public List<Map<String, Object>> fromExcel(Integer attributeIndex, InputStream inputStream, String[] titleColumns) {
        HSSFWorkbook workBook = null;
        try {
            workBook = new HSSFWorkbook(inputStream);
        } catch (IOException e) {
            log.error(e.toString(), e);
        }
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.rowIterator();

        List<Map<String, Object>> objectList = new ArrayList<>();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (isAllNull(row))
                continue;
            if (row.getRowNum() == attributeIndex) {
                continue;
            } else if (row.getRowNum() >= attributeIndex) {
                Map<String, Object> map = new HashMap<String, Object>();
                Iterator<Cell> cellIterator = row.cellIterator();
                int index = 0;
                while (cellIterator.hasNext() && index < titleColumns.length) {
                    Object value = cellIterator.next();
                    String key = titleColumns[index];
                    map.put(key, value);
                    index++;
                }
                objectList.add(map);
            }
        }
        return objectList;
    }

    /******************************************/
    // TODO: 2017/7/7 bug
    public List<Map<String, Object>> fromExcel(InputStream inputStream) {
        return fromExcel(0, DATE_PATTERN, inputStream);
    }

    public List<Map<String, Object>> fromExcel(Integer attributeIndex, InputStream inputStream) {
        return fromExcel(attributeIndex, DATE_PATTERN, inputStream);
    }

    public List<Map<String, Object>> fromExcel(String datePattern, InputStream inputStream) {
        return fromExcel(0, datePattern, inputStream);
    }

    public List<Map<String, Object>> fromExcel(Integer attributeIndex, String datePattern, InputStream inputStream) {
        HSSFWorkbook workBook = null;
        try {
            workBook = new HSSFWorkbook(inputStream);
        } catch (IOException e) {
            log.error(e.toString(), e);
        }
        List<T> rowList = new ArrayList<>();
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.rowIterator();

        Map<String, Integer> titleMap = new HashMap<>();

        List<Map<String, Object>> objectList = new ArrayList<>();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (isAllNull(row))
                continue;
            if (row.getRowNum() == attributeIndex) {
                // 解析map用的key,就是excel标题行
                Iterator<Cell> cellIterator = row.cellIterator();
                Integer index = 0;
                /*将行首字符转换为代表的column名与列的index组成的Map*/
                while (cellIterator.hasNext()) {
                    String value = cellIterator.next().getStringCellValue();
                    titleMap.put(value, index);
                    index++;
                }
                continue;
            } else if (row.getRowNum() >= attributeIndex) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (String k : titleMap.keySet()) {
                    String value = row.getCell(titleMap.get(k)).getStringCellValue();
                    map.put(k, value);
                }
                objectList.add(map);
            }
        }
        return objectList;
    }


    public boolean isAllNull(Row row) {
        // 整行都空，就跳过
        Iterator<Cell> cellIterator = row.cellIterator();
        boolean allRowIsNull = true;
        while (cellIterator.hasNext()) {
            Object cellValue = cellIterator.next().getStringCellValue();
            if (cellValue != null) {
                allRowIsNull = false;
                break;
            }
        }
        if (allRowIsNull) {
            log.warn("Excel row " + row.getRowNum() + " all row value is null!");
        }
        return allRowIsNull;
    }

    /**
     * 将多个Excel打包成zip文件
     *
     * @param srcfile
     * @param zipfile
     */
    public void zipFiles(List<File> srcfile, File zipfile) {
        byte[] buf = new byte[1024];
        try {
            // Create the ZIP file
            ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));
            // Compress the files
            for (int i = 0; i < srcfile.size(); i++) {
                File file = srcfile.get(i);
                FileInputStream in = new FileInputStream(file);
                // Add ZIP entry to output stream.
                out.putNextEntry(new ZipEntry(file.getName()));
                // Transfer bytes from the file to the ZIP file
                int len;
                while ((len = in.read(buf)) > 0) {
                    out.write(buf, 0, len);
                }
                // Complete the entry
                out.closeEntry();
                in.close();
            }
            // Complete the ZIP file
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 类似：new String("model".getBytes("utf-8")
     *
     * @param s
     * @return
     */
    public static String toUtf8String(String s) {

        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < s.length(); i++) {
            char c = s.charAt(i);
            if (c >= 0 && c <= 255) {
                sb.append(c);
            } else {
                byte[] b;
                try {
                    b = Character.toString(c).getBytes("utf-8");
                } catch (Exception ex) {
                    b = new byte[0];
                }
                for (int j = 0; j < b.length; j++) {
                    int k = b[j];
                    if (k < 0)
                        k += 256;
                    sb.append("%" + Integer.toHexString(k).toUpperCase());
                }
            }
        }
        return sb.toString();
    }

    public void downFile(OutputStream outs, String serverPath, String str) {
        try {
            String path = serverPath + str;
            File file = new File(path);
            if (file.exists()) {
                InputStream ins = new FileInputStream(path);
                BufferedInputStream bins = new BufferedInputStream(ins);// 放到缓冲流里面
                BufferedOutputStream bouts = new BufferedOutputStream(outs);

                int bytesRead = 0;
                byte[] buffer = new byte[8192];
                //开始向网络传输文件流
                while ((bytesRead = bins.read(buffer, 0, 8192)) != -1) {
                    bouts.write(buffer, 0, bytesRead);
                }
                bouts.flush();// 这里一定要调用flush()方法
                ins.close();
                bins.close();
                outs.close();
                bouts.close();
            } else {

            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 时间段headers （转化为“时间起点”至“时间终点”）
     *
     * @param startTime
     * @param endTime
     * @return
     */
    public String setTimeStr(Date startTime, Date endTime) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String sTime = null;
        String eTime = null;
        try {
            if (null != startTime) {
                sTime = sdf.format(startTime);
            }
        } catch (Exception e) {
            log.error(e);
        }
        try {
            if (null != endTime) {
                eTime = sdf.format(endTime);
            }
        } catch (Exception e) {
            log.error(e);
        }

        String time = "-";

        if (StringUtils.isNotBlank(sTime)) {
            if (StringUtils.isNotBlank(eTime)) {
                time = sTime + " 到 " + eTime;
            } else {
                time = sTime + " 到 " + sdf.format(new Date());
            }
        } else {
            if (StringUtils.isNotBlank(eTime)) {
                time = "开始营业" + " 到 " + eTime;
            } else {
                time = "开始营业" + " 到 " + sdf.format(new Date());
            }
        }
        return time;
    }


}
